package com.emp.dao;

import java.util.List;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.One;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.ResultMap;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import org.apache.ibatis.mapping.FetchType;
import org.springframework.stereotype.Repository;

import com.emp.bean.Emp;



//使用MyBatis框架只需要写接口定义
//功能不需要，写具体的实现,MyBatis会帮我们实现
@Repository //标识它是数据访问层 持久层 Dao
public interface EmpDao {
	 //查询所有的员工
	@Select(" select e.*,d.dname,d.location,m.ename mname "
			+ "from t_emp e left join t_dept d "
			+ " on e.deptno = d.deptno "
			+ " left join t_emp m "
			+ " on e.mgrno = m.empno ")
    @Results(id="empMap",value={
    		@Result(id=true,column="empno",property="empno"),
    		@Result(column="ename",property="ename"),
    		@Result(column="esex",property="esex"),
    		@Result(column="eage",property="eage"),
    		@Result(column="esalary",property="esalary"),
    		@Result(column="deptno",property="dept.deptno"),
    		@Result(column="dname",property="dept.dname"),
    		@Result(column="location",property="dept.location"), 		
    		@Result(column="mname",property="mgr.ename"),
    		@Result(column="mgrno",property="mgr.empno")
    })
	List<Emp> queryAll() throws Exception;
	
	//依据编号查询员工,带关联属性
	@Select(" select * from t_emp where empno=#{empno}")
	@Results(id="empMap3", value={
    		@Result(id=true,column="empno",property="empno"),
    		@Result(column="ename",property="ename"),
    		@Result(column="esex",property="esex"),
    		@Result(column="eage",property="eage"),
    		@Result(column="esalary",property="esalary"),
            //关联属性dept对象
    		@Result(column="deptno",property="dept",
    		one=@One(select="com.emp.dao.DeptDao.queryByDeptno",fetchType=FetchType.LAZY)),
    		//关联属性mgr
    		@Result(column="mgrno",property="mgr",one=@One(select="com.emp.dao.EmpDao.queryByEmpno"
    		,fetchType=FetchType.LAZY))
    })
	Emp queryByEmpno(String empno) throws Exception;
	
	
	
	
	@Select(" select e.*,d.dname,d.location,m.ename mname "
			+ " from t_emp e left join t_dept d "
			+ " on e.deptno = d.deptno "
			+ " left join t_emp m "
			+ " on e.mgrno = m.empno "
			+ " where e.empno = #{empno} ")
	@ResultMap("empMap")
	Emp queryByEmpno2(@Param("empno")String empno) throws Exception;
	
	
	//增
	@Insert(" insert into t_emp values(#{empno},#{ename},#{esex},#{eage},#{esalary},#{dept.deptno},#{mgr.empno})")
	void save(Emp emp) throws Exception;
	
	//删除
	//依据员工编号删除员工
	@Delete(" delete from t_emp where empno=#{empno}")
	void delete(String empno) throws Exception;
	//改
	@Update(" update t_emp set ename=#{ename},esex=#{esex},"
			+ " eage=#{eage},esalary=#{esalary},deptno=#{dept.deptno},mgrno=#{mgr.empno} "
			+ " where empno=#{empno} ")
	void update(Emp emp) throws Exception;//IBM Oracle EMC
	
	
	//依据部门编号查询所有员工
	@Select(" select * from t_emp where deptno=#{deptno} ")
	 @Results(id="empMap2",value={
	    		@Result(id=true,column="empno",property="empno"),
	    		@Result(column="ename",property="ename"),
	    		@Result(column="esex",property="esex"),
	    		@Result(column="eage",property="eage"),
	    		@Result(column="esalary",property="esalary"),
	    		@Result(column="deptno",property="dept.deptno"),
	    		@Result(column="mgrno",property="mgr.empno")
	    })
	List<Emp> queryByDeptno(String deptno) throws Exception;
	
	
	//四个条件组合成的模糊查询
	//ename   esex   deptno   mgrno  
	@Select("<script> select * from t_emp "
			+ "  <where> "
			+ " <if test=\"ename!=null and ename!='' \"> "
					+ " ename like CONCAT('%',#{ename},'%') "
					+ " </if>  "
					+ "  <if test=\"esex!=null and esex!=''\"> "
							+ "  and esex=#{esex} "
							+ " </if> "
							+ " <if test=\"deptno!=null and deptno!=''\"> "
									+ " and deptno=#{deptno} "
									+ " </if> "
									+ "   <if test=\"mgrno!=null and mgrno!=''\"> "
											+ " and mgrno = #{mgrno} "
											+ "  </if> "
											+ " </where> "
			+ "</script>")
	@ResultMap("empMap3")
	List<Emp> queryLike(@Param("ename") String ename,@Param("esex") String esex,
			  @Param("deptno") String deptno,@Param("mgrno") String mgrno)
	 throws Exception;
	
}
